package JDBC;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import SwingController.CmsPresenter;
import SwingModel.CmsMessage;
import SwingModel.CmsUser;

public class MessageActivities {
	
	public static List<CmsMessage> getListOfMessage(String username, String courseID){
		int messageId;
		int conversationId;
		String messageTitle;
		String messageContent;
		Date createdDTM;
		boolean isRead;
		boolean isSender;
		boolean isRecipient;
		String sender;
		List<CmsMessage> listOfMessages = new ArrayList<CmsMessage>();
		
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("SELECT A.MsgId, A.ConversationId, A.MsgTitle, A.MsgContent, A.ReceivedDTM, A.Sender, U.IsRead, U.IsSender, U.IsRecipient "
					+ "FROM Messages A, User_Msgs U WHERE A.CourseId = ? AND U.Username = ? AND U.isRecipient = 1 AND A.MsgId = U.MsgId GROUP BY A.MsgId, A.ConversationId DESC ORDER BY A.ReceivedDTM DESC");
			
			pstmt.setString(1, courseID);
			pstmt.setString(2, username );
			
			ResultSet resultSet = pstmt.executeQuery();
			
			List<Integer> temp_convId = new ArrayList<Integer>();
			
			while(resultSet.next()){
				//get message information
				messageId = resultSet.getInt("MsgId");
				conversationId = resultSet.getInt("ConversationId");
				messageTitle = resultSet.getString("MsgTitle");
				messageContent = resultSet.getString("MsgContent");
				createdDTM = resultSet.getTimestamp("ReceivedDTM");
				isRead = resultSet.getBoolean("IsRead");
				isSender = resultSet.getBoolean("IsSender");
				isRecipient = resultSet.getBoolean("IsRecipient");
				sender = resultSet.getString("Sender");
				
				//Remove duplicate conversation IDs, only display the most recent conversation
				boolean match = false;
				for(int cvID : temp_convId){
					if(conversationId == cvID){
						match = true;
					}
				}				
				if(!match){
					System.out.println("Message Id: " +  messageId + " Message Title: " + messageTitle);
					listOfMessages.add(new CmsMessage(messageId, conversationId, messageTitle, messageContent, createdDTM, isRead, isSender, isRecipient, sender));
				}
				temp_convId.add(conversationId);
				
			}
		} catch(Exception ex){
			System.out.println("Error: " + ex.toString());
		}
		
		return listOfMessages;
	}
	
	public static void sendMessage(String courseID, CmsMessage msg, List<CmsUser> recipients){ // change to sendMessage (courseID, msg, isReply, recipients)
		int conversationId = msg.getConversationID();
		String messageTitle = msg.getMessageTitle();
		String messageContent = msg.getMessageContent();
		String username = CmsPresenter.user.getUsername();
		 
	
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			
			PreparedStatement pstmt1 = dCon.getPreparedStatement();
			pstmt1 = dCon.connection.prepareStatement("INSERT INTO Messages (CourseId, ConversationId, MsgTitle, MsgContent, ReceivedDTM, Sender) VALUES (?, ?, ?, ?, Now(), ?);");
			pstmt1.setString(1, courseID);
			pstmt1.setInt(2, conversationId);
			pstmt1.setString(3, messageTitle);
			pstmt1.setString(4, messageContent);
			pstmt1.setString(5, username);
			pstmt1.execute();
			
			System.out.println("Successfully inserted message into Messages table");
				
			PreparedStatement pstmt2 = dCon.getPreparedStatement();
			pstmt2 = dCon.connection.prepareStatement("SELECT MAX(MsgId) from Messages;");
			ResultSet resultSet = pstmt2.executeQuery();
			
			if(resultSet.next()){
				int insertedMsgId = resultSet.getInt(1);;
				System.out.println("Successefully inserted! The inserted message Id is " + insertedMsgId);
				
				//Update the conversation ID for the newly inserted message. Set it to the message ID
				if(conversationId == 0){
					PreparedStatement pstmt = dCon.getPreparedStatement();
					pstmt = dCon.connection.prepareStatement("UPDATE Messages SET ConversationId = ? WHERE MsgId = ?;");
					pstmt.setInt(1, insertedMsgId);
					pstmt.setInt(2, insertedMsgId);
					pstmt.execute();
				}
				
				//Assign message to sender
				PreparedStatement pstmt3 = dCon.getPreparedStatement();
				pstmt3 = dCon.connection.prepareStatement("INSERT INTO User_Msgs (MsgId, Username, IsRead, IsSender, IsRecipient) VALUES (?, ?, 1, 1, 0);");
				pstmt3.setInt(1, insertedMsgId);
				pstmt3.setString(2, username);		
				pstmt3.execute();
				
				//Assign message to recipients				
				PreparedStatement pstmt4 = dCon.getPreparedStatement();
				
				for(CmsUser recip : recipients){
					if(! username.equals(recip.getUsername())){ //user cannot send msg to him/herself
						pstmt4 = dCon.connection.prepareStatement("INSERT INTO User_Msgs (MsgId, Username, IsRead, IsSender, IsRecipient) VALUES (?, ?, 0, 0, 1);");
						pstmt4.setInt(1, insertedMsgId);
						pstmt4.setString(2, recip.getUsername());
						pstmt4.execute();
					}
				}
				
				System.out.println("Message successfully created!");

			} else {
				System.out.println("\nUnsuccessful query retrieving the inserted message ID!\n");
			}
			dCon.close();
		} catch(Exception ex){
			System.out.println("\nUnsuccessful query while inserting a new message into the Messages table!\n");
			System.out.println("Error Message: " + ex.toString());
		}
	}
	
	public static void replyMessage(String courseID, CmsMessage msg, String sender){ // change to sendMessage (courseID, msg, isReply, recipients)
		int conversationId = msg.getConversationID();
		String messageTitle = msg.getMessageTitle();
		String messageContent = msg.getMessageContent();
		String username = CmsPresenter.user.getUsername();
		 
	
		try{
			//Database Connection and Retrieve data
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			
			PreparedStatement pstmt1 = dCon.getPreparedStatement();
			pstmt1 = dCon.connection.prepareStatement("INSERT INTO Messages (CourseId, ConversationId, MsgTitle, MsgContent, ReceivedDTM, Sender) VALUES (?, ?, ?, ?, Now(), ?);");
			pstmt1.setString(1, courseID);
			pstmt1.setInt(2, conversationId);
			pstmt1.setString(3, messageTitle);
			pstmt1.setString(4, messageContent);
			pstmt1.setString(5, username);
			pstmt1.execute();
			
			System.out.println("Successfully inserted message into Messages table");
				
			PreparedStatement pstmt2 = dCon.getPreparedStatement();
			pstmt2 = dCon.connection.prepareStatement("SELECT MAX(MsgId) from Messages;");
			ResultSet resultSet = pstmt2.executeQuery();
			
			if(resultSet.next()){
				int insertedMsgId = resultSet.getInt(1);;
				System.out.println("Successefully inserted! The inserted message Id is " + insertedMsgId);
				
				//Update the conversation ID for the newly inserted message. Set it to the message ID
				if(conversationId == 0){
					PreparedStatement pstmt = dCon.getPreparedStatement();
					pstmt = dCon.connection.prepareStatement("UPDATE Messages SET ConversationId = ? WHERE MsgId = ?;");
					pstmt.setInt(1, insertedMsgId);
					pstmt.setInt(2, insertedMsgId);
					pstmt.execute();
				}
				
				//Assign message to sender
				PreparedStatement pstmt3 = dCon.getPreparedStatement();
				pstmt3 = dCon.connection.prepareStatement("INSERT INTO User_Msgs (MsgId, Username, IsRead, IsSender, IsRecipient) VALUES (?, ?, 1, 1, 0);");
				pstmt3.setInt(1, insertedMsgId);
				pstmt3.setString(2, username);		
				pstmt3.execute();
				
				//Assign message to recipients				
				PreparedStatement pstmt4 = dCon.getPreparedStatement();
				
				pstmt4 = dCon.connection.prepareStatement("INSERT INTO User_Msgs (MsgId, Username, IsRead, IsSender, IsRecipient) VALUES (?, ?, 0, 0, 1);");
				pstmt4.setInt(1, insertedMsgId);
				pstmt4.setString(2, sender);
				pstmt4.execute();
				
				
				System.out.println("Message successfully created!");

			} else {
				System.out.println("\nUnsuccessful query retrieving the inserted message ID!\n");
			}
			dCon.close();
		} catch(Exception ex){
			System.out.println("\nUnsuccessful query while inserting a new message into the Messages table!\n");
			System.out.println("Error Message: " + ex.toString());
		}
	}
	
	public static void deleteMessage(String userID, int conversationID){
		
		//userID is not needed
		try {
			System.out.println("Delete Conversation ID: " + conversationID);
			CmsDatabaseConnection dCon = new CmsDatabaseConnection();
			
			PreparedStatement pstmt = dCon.getPreparedStatement();
			pstmt = dCon.connection.prepareStatement("DELETE U FROM User_Msgs U, Messages M WHERE U.Username = ? AND M.ConversationId = ? AND U.MsgId = M.MsgId;");
			pstmt.setString(1, userID);
			pstmt.setInt(2, conversationID);
			pstmt.execute();
			dCon.close();
			} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error: " + e.getMessage().toString());
		}
	}
	
	public static void setRead(String userID, int messageID){
		CmsDatabaseConnection dCon = new CmsDatabaseConnection();
		PreparedStatement pstmt5 = dCon.getPreparedStatement();
		
		try { 
			pstmt5 = dCon.connection.prepareStatement("UPDATE User_Msgs SET IsRead = 1 WHERE MsgId = ? AND Username = ?;");
			pstmt5.setInt(1, messageID);
			pstmt5.setString(2, userID);
			pstmt5.execute();
			} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("Error: " + e.getMessage().toString());
		}
		
		dCon.close();
	}
}
